package com.cantierinformatici.data;

import java.util.Date;

import com.cantierinformatici.mygym.business.Attrezzo;
import com.cantierinformatici.mygym.business.Esercizio;
import com.cantierinformatici.mygym.business.Attivita;
import com.cantierinformatici.mygym.business.Muscolo;
import com.cantierinformatici.mygym.business.PesoCorporeo;
import com.cantierinformatici.mygym.business.Serie;
import com.cantierinformatici.mygym.data.sqlite.DbHelper;
import com.cantierinformatici.mygym.data.sqlite.Contract.AttivitaEntry;
import com.cantierinformatici.mygym.data.sqlite.Contract.AttrezzoEntry;
import com.cantierinformatici.mygym.data.sqlite.Contract.EsercizioEntry;
import com.cantierinformatici.mygym.data.sqlite.Contract.MuscoloEntry;
import com.cantierinformatici.mygym.data.sqlite.Contract.PesoCorporeoEntry;
import com.cantierinformatici.mygym.data.sqlite.Contract.SerieEntry;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DbFacade {

	public final String TAG = "mygym.DbFacade";
	private static SQLiteDatabase db;
	private static Cursor cursor;
	private static DbHelper helper;

	public static void close() {
		try {
			cursor.close();
		} catch (Exception ex) {
			System.out.println("close cursor error:");
			ex.printStackTrace();
		}
		try {
			helper.close();
			
		} catch (Exception ex) {
			System.out.println("close helper error:");
			ex.printStackTrace();
		}
		try {
			// db.close();
		} catch (Exception ex) {
			System.out.println("close db error:");
			ex.printStackTrace();
		}

	}

	public static long store(Context context, Attrezzo attrezzo) {
		close();
		helper = new DbHelper(context);
		db = helper.getWritableDatabase();
		long mRowId;

		ContentValues values = new ContentValues();
		values.put(AttrezzoEntry.COLUMN_NAME_NOME, attrezzo.Nome);
		values.put(AttrezzoEntry.COLUMN_NAME_FOTO, attrezzo.Foto);
		values.put(AttrezzoEntry.COLUMN_NAME_SYNC, (attrezzo.Sync ? 1 : 0));

		if (Long.toString(attrezzo.Id) == null || attrezzo.Id == 0) {
			mRowId = db.insert(AttrezzoEntry.TABLE_NAME, null, values);

		} else {
			String selection = AttrezzoEntry._ID + "=?";
			String[] selectionArgs = new String[] { String.valueOf(attrezzo.Id) };
			mRowId = db.update(AttrezzoEntry.TABLE_NAME, values, selection,
					selectionArgs);
		}
		return mRowId;
	}

	public static Cursor get(Context context, Attrezzo[] aArr) {
		helper = new DbHelper(context);
		db = helper.getReadableDatabase();

		String selection = null;
		String[] selectionArgs = null;
		String sortOrder = AttrezzoEntry.COLUMN_NAME_NOME + " ASC";

		cursor = db.query(AttrezzoEntry.TABLE_NAME, 
				AttrezzoEntry.COLUMNS_PROJECTION, 
				selection, 
				selectionArgs,
				null, 
				null, 
				sortOrder
				);

		return cursor;
	}

	public static Cursor get(Context context, Attrezzo attrezzo) {
		helper = new DbHelper(context);
		db = helper.getReadableDatabase();

		String selection = AttrezzoEntry._ID + "=?";
		String[] selectionArgs = new String[] { String.valueOf(attrezzo.Id) };
		String sortOrder = AttrezzoEntry.COLUMN_NAME_NOME + " ASC";

		cursor = db.query(AttrezzoEntry.TABLE_NAME,
				AttrezzoEntry.COLUMNS_PROJECTION, 
				selection, 
				selectionArgs,
				null, 
				null, 
				sortOrder
				);
		return cursor;
	}

	public static int delete(Context context, Attrezzo attrezzo)
			throws Exception {
		helper = new DbHelper(context);
		db = helper.getWritableDatabase();

		String selection = AttrezzoEntry._ID + "=?";
		String[] selectionArgs = new String[] { String.valueOf(attrezzo.Id) };

		int count = db.delete(AttrezzoEntry.TABLE_NAME, 
				selection, 
				selectionArgs
				);
		return count;
	}

	public static long store(Context context, Esercizio esercizio) {
		helper = new DbHelper(context);
		db = helper.getWritableDatabase();

		ContentValues values = new ContentValues();
		values.put(EsercizioEntry.COLUMN_NAME_NOME, esercizio.Nome);
		values.put(EsercizioEntry.COLUMN_NAME_NOTE, esercizio.Descrizione);
		values.put(EsercizioEntry.COLUMN_NAME_ATTREZZO_ID,
				esercizio.Attrezzo.Id);
		values.put(EsercizioEntry.COLUMN_NAME_MUSCOLO_ID, esercizio.Muscolo.Id);
		values.put(EsercizioEntry.COLUMN_NAME_SYNC, (esercizio.Sync ? 1 : 0));

		long mRowId;

		if (Long.toBinaryString(esercizio.Id) == null || esercizio.Id == 0) {
			mRowId = db.insert(EsercizioEntry.TABLE_NAME, null, values);
		} else {
			String selection = EsercizioEntry._ID + "=?";
			String[] selectionArgs = new String[] { String
					.valueOf(esercizio.Id) };
			mRowId = db.update(EsercizioEntry.TABLE_NAME, values, selection,
					selectionArgs);
		}

		return mRowId;
	}

	public static Cursor get(Context context, Esercizio[] mArr) {
		helper = new DbHelper(context);
		db = helper.getReadableDatabase();

		String query = "select a.*,b.*,c.* from %s a,%s b,%s c where a.%s=b.%s AND a.%s=c.%s ORDER BY a.%s ASC";
		String args[] = new String[] { EsercizioEntry.TABLE_NAME, // a
				AttrezzoEntry.TABLE_NAME, // b
				MuscoloEntry.TABLE_NAME, // c
				EsercizioEntry.COLUMN_NAME_ATTREZZO_ID, // a
				AttrezzoEntry._ID, // b
				EsercizioEntry.COLUMN_NAME_ATTREZZO_ID, // a
				MuscoloEntry._ID, // c
				EsercizioEntry.COLUMN_NAME_NOME }; // a

		query = String.format(query, (Object[]) args);
		cursor = db.rawQuery(query, null);

		return cursor;
	}

	public static Cursor get(Context context, Esercizio esercizio) {
		helper = new DbHelper(context);
		db = helper.getReadableDatabase();

		
		String query = "select a.*,b.*,c.* from %s a,%s b,%s c where a.%s=b.%s AND a.%s=c.%s WHERE a.%s=? ORDER BY a.%s ASC";
		String args[] = new String[] { EsercizioEntry.TABLE_NAME, // a
				AttrezzoEntry.TABLE_NAME, // b
				MuscoloEntry.TABLE_NAME, // c
				EsercizioEntry.COLUMN_NAME_ATTREZZO_ID, // a
				AttrezzoEntry._ID, // b
				EsercizioEntry.COLUMN_NAME_ATTREZZO_ID, // a
				MuscoloEntry._ID, // c
				EsercizioEntry.COLUMN_NAME_NOME }; // a

		query = String.format(query, (Object[]) args);
		cursor = db.rawQuery(query, null);
		
		String selection = EsercizioEntry._ID + "=?";
		String[] selectionArgs = new String[] { String.valueOf(esercizio.Id) };
		String sortOrder = EsercizioEntry.COLUMN_NAME_NOME + " ASC";

		cursor = db.query(EsercizioEntry.TABLE_NAME,
				EsercizioEntry.COLUMNS_PROJECTION, 
				selection, 
				selectionArgs,
				null, 
				null, 
				sortOrder
				);

		return cursor;
	}

	public static int delete(Context context, Esercizio esercizio) {
		helper = new DbHelper(context);
		db = helper.getWritableDatabase();

		String selection = EsercizioEntry._ID + "=?";
		String[] selectionArgs = new String[] { String.valueOf(esercizio.Id) };

		int count = db.delete(EsercizioEntry.TABLE_NAME, selection,
				selectionArgs);
		return count;
	}

	public static long insert(Context context, Attivita attivita) {
		helper = new DbHelper(context);
		db = helper.getWritableDatabase();

		ContentValues values = new ContentValues();
		values.put(AttivitaEntry.COLUMN_NAME_ESERCIZIO_ID,
				attivita.Esercizio.Id);
		values.put(AttivitaEntry.COLUMN_NAME_DATE, Date2String(attivita.Data));
		values.put(AttivitaEntry.COLUMN_NAME_SYNC, (attivita.Sync ? 1 : 0));
		values.put(AttivitaEntry.COLUMN_NAME_NOTE, attivita.Nota);

		long newRowId;
		newRowId = db.insert(AttivitaEntry.TABLE_NAME, null, values);
		return newRowId;
	}

	public static Cursor get(Context context, Attivita[] mArr) throws Exception {
		helper = new DbHelper(context);
		db = helper.getReadableDatabase();

		String selection = null;
		String[] selectionArgs = null;
		String sortOrder = EsercizioEntry.COLUMN_NAME_NOME + " ASC";

		cursor = db.query(EsercizioEntry.TABLE_NAME, 
				EsercizioEntry.COLUMNS_PROJECTION, 
				selection, 
				selectionArgs,
				null, 
				null, 
				sortOrder
				);
		return cursor;
	}

	public static Cursor get(Context context, Attivita esercizio)
			throws Exception {
		helper = new DbHelper(context);
		db = helper.getReadableDatabase();

		String selection = AttivitaEntry._ID + "=?";
		String[] selectionArgs = new String[] { String.valueOf(esercizio.Id) };
		String sortOrder = AttivitaEntry.COLUMN_NAME_DATE + " ASC";

		cursor = db.query(EsercizioEntry.TABLE_NAME,
				EsercizioEntry.COLUMNS_PROJECTION, 
				selection, 
				selectionArgs,
				null, 
				null, 
				sortOrder
				);

		return cursor;
	}

	public static long store(Context context, Muscolo muscolo) {
		helper = new DbHelper(context);
		db = helper.getWritableDatabase();
		long mRowId;

		// Create a new map of values, where column names are the keys
		ContentValues values = new ContentValues();
		values.put(MuscoloEntry.COLUMN_NAME_NOME, muscolo.Nome);
		values.put(MuscoloEntry.COLUMN_NAME_FOTO, muscolo.Foto);
		values.put(MuscoloEntry.COLUMN_NAME_SYNC, (muscolo.Sync ? 1 : 0));

		// Insert the new row, returning the primary key value of the new row

		if (Long.toBinaryString(muscolo.Id) == null || muscolo.Id == 0) {
			mRowId = db.insert(MuscoloEntry.TABLE_NAME, null, values);
		} else {
			String selection = MuscoloEntry._ID + "=?";
			String[] selectionArgs = new String[] { String.valueOf(muscolo.Id) };
			mRowId = db.update(MuscoloEntry.TABLE_NAME, values, selection,
					selectionArgs);
		}

		return mRowId;
	}

	public static Cursor get(Context context, Muscolo[] mArr) {
		helper = new DbHelper(context);
		db = helper.getReadableDatabase();

		String selection = null;
		String[] selectionArgs = null;
		String sortOrder = MuscoloEntry.COLUMN_NAME_NOME + " ASC";

		cursor = db.query(MuscoloEntry.TABLE_NAME, 
				MuscoloEntry.COLUMNS_PROJECTION, 
				selection, 
				selectionArgs,
				null, 
				null, 
				sortOrder
				);

		return cursor;
	}

	public static Cursor get(Context context, Muscolo muscolo) {
		helper = new DbHelper(context);
		db = helper.getReadableDatabase();

		String selection = MuscoloEntry._ID + "=?";
		String[] selectionArgs = new String[] { String.valueOf(muscolo.Id) };
		String sortOrder = MuscoloEntry.COLUMN_NAME_NOME + " ASC";

		cursor = db.query(MuscoloEntry.TABLE_NAME, 
				MuscoloEntry.COLUMNS_PROJECTION,
				selection, 
				selectionArgs,
				null, 
				null, 
				sortOrder
				);

		return cursor;
	}

	public static int delete(Context context, Muscolo muscolo) throws Exception {
		helper = new DbHelper(context);
		db = helper.getWritableDatabase();

		String selection = MuscoloEntry._ID + "=?";
		String[] selectionArgs = new String[] { String.valueOf(muscolo.Id) };

		int count = db.delete(MuscoloEntry.TABLE_NAME, 
				selection, 
				selectionArgs
				);
		return count;
	}

	public static long insert(Context context, PesoCorporeo peso) {

		helper = new DbHelper(context);
		db = helper.getWritableDatabase();

		ContentValues values = new ContentValues();
		values.put(PesoCorporeoEntry.COLUMN_NAME_DATE, Date2String(peso.Data));
		values.put(PesoCorporeoEntry.COLUMN_NAME_SYNC, (peso.Sync ? 1 : 0));
		values.put(PesoCorporeoEntry.COLUMN_NAME_WEIGHT, peso.Peso);

		long newRowId;
		newRowId = db.insert(PesoCorporeoEntry.TABLE_NAME, null, values);
		return newRowId;
	}

	public static Cursor get(Context context, PesoCorporeo[] pArr) {
		helper = new DbHelper(context);
		db = helper.getReadableDatabase();

		String selection = null;
		String[] selectionArgs = null;
		String sortOrder = PesoCorporeoEntry.COLUMN_NAME_DATE + " ASC";

		cursor = db.query(PesoCorporeoEntry.TABLE_NAME, 
				PesoCorporeoEntry.COLUMNS_PROJECTION, 
				selection, 
				selectionArgs, 
				null, 
				null,
				sortOrder
				);

		return cursor;
	}

	public static Cursor get(Context context, PesoCorporeo peso)
			throws Exception {

		helper = new DbHelper(context);
		db = helper.getReadableDatabase();

		String selection = PesoCorporeoEntry._ID + "=?";
		String[] selectionArgs = new String[] { String.valueOf(peso.Id) };
		String sortOrder = AttivitaEntry.COLUMN_NAME_DATE + " ASC";

		cursor = db.query(EsercizioEntry.TABLE_NAME,
				PesoCorporeoEntry.COLUMNS_PROJECTION, 
				selection,
				selectionArgs,
				null, 
				null, 
				sortOrder 
				);

		return cursor;
	}

	public static long insert(Context context, Serie serie) {
		helper = new DbHelper(context);
		db = helper.getWritableDatabase();

		ContentValues values = new ContentValues();
		values.put(SerieEntry.COLUMN_NAME_RIPETITIONS, serie.Ripetizioni);
		values.put(SerieEntry.COLUMN_NAME_SYNC, (serie.Sync ? 1 : 0));
		values.put(SerieEntry.COLUMN_NAME_WEIGHT, serie.Peso);
		values.put(SerieEntry.COLUMN_NAME_ATTIVITA_ID, serie.EsercizioSvolto.Id);

		long newRowId;
		newRowId = db.insert(SerieEntry.TABLE_NAME, null, values);

		return newRowId;
	}

	public static Cursor get(Context context, Serie[] sArr) throws Exception {
		helper = new DbHelper(context);
		db = helper.getReadableDatabase();

		String selection = null;
		String[] selectionArgs = null;
		String sortOrder = SerieEntry._ID + " ASC";

		cursor = db.query(SerieEntry.TABLE_NAME, 
				SerieEntry.COLUMNS_PROJECTION, 
				selection, 
				selectionArgs,
				null, 
				null, 
				sortOrder
				);

		return cursor;
	}

	public static Cursor get(Context context, Serie serie) throws Exception {
		helper = new DbHelper(context);
		db = helper.getReadableDatabase();

		String selection = SerieEntry._ID + "=?";
		String[] selectionArgs = new String[] { String.valueOf(serie.Id) };
		String sortOrder = AttivitaEntry._ID + " ASC";

		cursor = db.query(EsercizioEntry.TABLE_NAME, 
				SerieEntry.COLUMNS_PROJECTION, 
				selection, 
				selectionArgs,
				null, 
				null, 
				sortOrder
				);

		return cursor;
	}

	public static Cursor getSerieByAttivitaId(Context context, int attivita_id)
			throws Exception {
		helper = new DbHelper(context);
		db = helper.getReadableDatabase();

		String selection = SerieEntry.COLUMN_NAME_ATTIVITA_ID + "=?";
		String[] selectionArgs = new String[] { String.valueOf(attivita_id) };
		String sortOrder = AttivitaEntry._ID + " ASC";

		cursor = db.query(EsercizioEntry.TABLE_NAME, 
				SerieEntry.COLUMNS_PROJECTION,
				selection, 
				selectionArgs,
				null, 
				null, 
				sortOrder
				);

		return cursor;
	}

	/**
	 * Converte una data dal formato Date al formato String da salvare nel db
	 * 
	 * @param data
	 * @return data di tipo String
	 */
	public static String Date2String(Date data) {
		return null;
	}

	/**
	 * Converte una data dal formato testuale (String) al formato Date
	 * 
	 * @param data
	 * @return data di tipo Date
	 */
	public static Date String2Date(String data) {
		return null;
	}
}
